package com.poi.demopoi.controller2;


import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

/**
 * @Author: yh
 * @Description: Excel导入轮子
 * @Date: Created in 9:44  2018/6/29.
 */

public class ExcelUtils {

    public static <T> List<T> toList(String path, Class<T> clazz, ExcelFieldMap[] fields, int headerRows) throws IOException, InvocationTargetException, InstantiationException, IllegalAccessException {
        if (StringUtils.equals(FilenameUtils.getExtension(path), "xls")) {
            return xlsToList(new FileInputStream(path), clazz, fields, headerRows);
        } else {
            return xlsxToList(new FileInputStream(path), clazz, fields, headerRows);
        }
    }


    public static class ExcelFieldMap {
        private int excelColumn;
        private String fieldName;

        public ExcelFieldMap(int excelColumn, String fieldName) {
            this.excelColumn = excelColumn;
            this.fieldName = fieldName;
        }

        public String getFieldName() {
            return fieldName;
        }

        public void setFieldName(String fieldName) {
            this.fieldName = fieldName;
        }

        public int getExcelColumn() {
            return excelColumn;
        }

        public void setExcelColumn(int excelColumn) {
            this.excelColumn = excelColumn;
        }
    }

    // .xls
    private static <T> List<T> xlsToList(InputStream inputStream, Class<T> clazz, ExcelFieldMap[] fields, int headerRows) throws IOException, IllegalAccessException, InstantiationException, InvocationTargetException {
        HSSFWorkbook wb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;
        Iterator rows = sheet.rowIterator();
        List<T> list = new ArrayList<>();
        for (int i = 0; rows.hasNext(); i++) {
            row = (HSSFRow) rows.next();
            if (i <= headerRows) {
                continue;
            }
            T obj = clazz.newInstance();
            for (ExcelFieldMap excelFieldMap : fields) {
                cell = row.getCell(excelFieldMap.excelColumn);
                if (cell == null) {
                    continue;
                }
                if (cell.getCellTypeEnum() == CellType.STRING) {
                    BeanUtils.setProperty(obj, excelFieldMap.fieldName,  cell.getStringCellValue());
                } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    BeanUtils.setProperty(obj, excelFieldMap.fieldName, (int)cell.getNumericCellValue());
                } else {
                }
            }

            list.add(obj);
        }
        return list;
    }

    // .xlsx
    private static <T> List<T> xlsxToList(InputStream inputStream, Class<T> clazz, ExcelFieldMap[] fields, int headerRows) throws IOException, IllegalAccessException, InstantiationException, InvocationTargetException {
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        Iterator rows = sheet.rowIterator();
        List<T> list = new ArrayList<>();
        for (int i = 0; rows.hasNext(); i++) {
            row = (XSSFRow) rows.next();
            if (i <= headerRows) {
                continue;
            }
            T obj = clazz.newInstance();
            for (ExcelFieldMap excelFieldMap : fields) {
                cell = row.getCell(excelFieldMap.excelColumn);
                if (cell == null) {
                    continue;
                }
                if (cell.getCellTypeEnum() == CellType.STRING) {
                    BeanUtils.setProperty(obj, excelFieldMap.fieldName, cell.getStringCellValue());
                } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    BeanUtils.setProperty(obj, excelFieldMap.fieldName, (int) cell.getNumericCellValue());
                } else {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            list.add(obj);
        }
        return list;
    }
}
